package health_system.health_system.repository;

import health_system.health_system.entity.Moment;
import health_system.health_system.entity.MomentComment;
import health_system.health_system.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MomentCommentRepository extends JpaRepository<MomentComment, Long> {
    
    // Find all comments for a moment
    List<MomentComment> findByMomentOrderByCreatedAtAsc(Moment moment);
    
    // Find with pagination
    Page<MomentComment> findByMomentOrderByCreatedAtAsc(Moment moment, Pageable pageable);
    
    // Find all comments by a user
    List<MomentComment> findByUserOrderByCreatedAtDesc(User user);
    
    // Find with pagination
    Page<MomentComment> findByUserOrderByCreatedAtDesc(User user, Pageable pageable);
    
    // Find all moments commented by a user
    @Query("SELECT DISTINCT mc.moment FROM MomentComment mc WHERE mc.user = :user ORDER BY MAX(mc.createdAt) DESC")
    List<Moment> findMomentsCommentedByUser(@Param("user") User user);
    
    // Find with pagination
    @Query("SELECT DISTINCT mc.moment FROM MomentComment mc WHERE mc.user = :user GROUP BY mc.moment ORDER BY MAX(mc.createdAt) DESC")
    Page<Moment> findMomentsCommentedByUser(@Param("user") User user, Pageable pageable);
    
    // Find replies to a comment
    List<MomentComment> findByParentOrderByCreatedAtAsc(MomentComment parent);
    
    // Count comments for a moment
    Long countByMoment(Moment moment);
    
    // Count comments by user
    Long countByUser(User user);
    
    // Count replies to a comment
    Long countByParent(MomentComment parent);
    
    // Find users who commented on a moment
    @Query("SELECT DISTINCT mc.user FROM MomentComment mc WHERE mc.moment = :moment ORDER BY MIN(mc.createdAt)")
    List<User> findUsersWhoCommentedOnMoment(@Param("moment") Moment moment);
    
    // Search comments by content
    @Query("SELECT mc FROM MomentComment mc WHERE mc.moment = :moment AND mc.content LIKE %:searchTerm% ORDER BY mc.createdAt ASC")
    List<MomentComment> searchCommentsInMoment(@Param("moment") Moment moment, @Param("searchTerm") String searchTerm);
    
    // Find comments with most replies
    @Query("SELECT mc, COUNT(reply) FROM MomentComment mc LEFT JOIN MomentComment reply ON reply.parent = mc " +
           "WHERE mc.moment = :moment GROUP BY mc ORDER BY COUNT(reply) DESC")
    List<Object[]> findCommentsWithMostReplies(@Param("moment") Moment moment, Pageable pageable);
    
    // Find comments containing mentions of a user
    @Query("SELECT mc FROM MomentComment mc WHERE mc.content LIKE %:mention% AND " +
           "(" +
           "(mc.moment.visibility = 'PUBLIC') OR " +
           "(mc.moment.user = :user) OR " +
           "(mc.moment.visibility = 'FRIENDS' AND " +
           "EXISTS (SELECT f FROM Friendship f WHERE " +
           "((f.requester = mc.moment.user AND f.addressee = :user) OR " +
           "(f.requester = :user AND f.addressee = mc.moment.user)) AND f.status = 'ACCEPTED')) OR " +
           "(mc.moment.visibility = 'CUSTOM' AND :user MEMBER OF mc.moment.visibleToUsers)" +
           ") " +
           "ORDER BY mc.createdAt DESC")
    List<MomentComment> findCommentsMentioningUser(@Param("user") User user, @Param("mention") String mention);
    
    // Find most recently commented moments by a user's friends
    @Query("SELECT mc.moment FROM MomentComment mc WHERE mc.user IN " +
           "(SELECT CASE WHEN f.requester = :user THEN f.addressee ELSE f.requester END " +
           "FROM Friendship f WHERE (f.requester = :user OR f.addressee = :user) AND f.status = 'ACCEPTED') " +
           "AND mc.moment.visibility IN ('PUBLIC', 'FRIENDS') " +
           "GROUP BY mc.moment ORDER BY MAX(mc.createdAt) DESC")
    List<Moment> findRecentlyCommentedMomentsByFriends(@Param("user") User user, Pageable pageable);
    
    // Find moments with most comments in user's visible moments
    @Query("SELECT m, COUNT(mc) FROM Moment m LEFT JOIN m.comments mc WHERE " +
           // Same visibility filters as in MomentRepository
           "(" +
           "(m.visibility = 'PUBLIC') OR " +
           "(m.user = :viewer) OR " +
           "(m.visibility = 'FRIENDS' AND " +
           "EXISTS (SELECT f FROM Friendship f WHERE " +
           "((f.requester = m.user AND f.addressee = :viewer) OR " +
           "(f.requester = :viewer AND f.addressee = m.user)) AND f.status = 'ACCEPTED')) OR " +
           "(m.visibility = 'CUSTOM' AND :viewer MEMBER OF m.visibleToUsers)" +
           ") " +
           "GROUP BY m ORDER BY COUNT(mc) DESC")
    List<Object[]> findMomentsWithMostCommentsVisibleToUser(@Param("viewer") User viewer, Pageable pageable);
} 